import os
import re
import pandas as pd
from datetime import datetime
from dateutil.relativedelta import relativedelta  # 额外引入
import plotly.express as px

# 目录配置
MONTH_EVAL_DIR = r"E:\工作临时文件\Evaluate_data\Month_data\month_workers_evaluate"
IMAGE_RESULT_DIR = r"E:\工作临时文件\Evaluate_data\image_result"


def extract_month_from_filename(filename: str):
    """
    从文件名提取 YYYY-MM 形式的月份
    e.g. '2024年08月员工绩效系数.xlsx' -> '2024-08'
    """
    m = re.search(r"(\d{4})年(\d{2})月", filename)
    if m:
        return f"{m.group(1)}-{m.group(2)}"
    return None


def load_all_monthly_data():
    """
    读取目录下所有 'xx年xx月员工绩效系数.xlsx' 文件，返回合并DataFrame
    """
    all_rows = []
    for fname in os.listdir(MONTH_EVAL_DIR):
        if fname.endswith("员工绩效系数.xlsx"):
            month_label = extract_month_from_filename(fname)
            if not month_label:
                continue

            fpath = os.path.join(MONTH_EVAL_DIR, fname)
            try:
                df = pd.read_excel(fpath)
                if "员工" in df.columns and "月度绩效得分" in df.columns:
                    for _, row in df.iterrows():
                        all_rows.append({
                            "月份": month_label,
                            "员工": str(row["员工"]).strip(),
                            "绩效系数": float(row["月度绩效得分"])
                        })
            except Exception as e:
                print(f"⚠️ 无法读取文件 {fpath}: {e}")

    return pd.DataFrame(all_rows)


def plot_employee_bar_with_table(df_all, employee):
    df_emp = df_all[df_all["员工"] == employee].copy()

    # 按月份排序
    df_emp["月份排序"] = pd.to_datetime(df_emp["月份"] + "-01")
    df_emp.sort_values("月份排序", inplace=True)

    # 只取最近12个月
    df_emp = df_emp.tail(12)

    # ===== 1️⃣ 绘制柱状图 =====
    fig = px.bar(
        df_emp,
        x="月份",
        y="绩效系数",
        text="绩效系数",
        title=f"{employee} 最近12个月绩效系数",
        labels={"绩效系数": "绩效系数值", "月份": "月份"},
        height=700, width=1180
    )
    fig.update_traces(texttemplate='%{text:.2f}', textposition='outside', marker=dict(color='green'))
    fig.update_layout(
        xaxis=dict(categoryorder='category ascending'),
        yaxis=dict(title="绩效系数"),
        showlegend=False,
        margin=dict(l=50, r=50, t=80, b=50)
    )

    fig_html = fig.to_html(full_html=False, include_plotlyjs='cdn')

    # ===== 2️⃣ 最近12个月绩效表格 =====
    df_table = df_emp[["月份", "绩效系数"]].copy()
    df_table["绩效系数"] = df_table["绩效系数"].map(lambda x: f"{x:.2f}")
    table_html = df_table.to_html(index=False)

    # ===== 3️⃣ 拼接HTML，居中对齐 =====
    html_content = f"""
    <html>
    <head>
        <meta charset="utf-8">
        <title>{employee} 最近12个月绩效系数</title>
        <style>
            body {{
                font-family: Arial, sans-serif; 
                margin: 40px;
                text-align: center;
            }}
            h1, h2 {{
                text-align: center;
            }}
            table {{
                margin: 20px auto;
                border-collapse: collapse;
                width: 60%;
            }}
            th, td {{
                border: 1px solid #ddd;
                text-align: center;
                padding: 8px;
            }}
            th {{
                background-color: #f2f2f2;
            }}
        </style>
    </head>
    <body>
        <h1>{employee} 最近12个月绩效系数</h1>

        <!-- 图表居中 -->
        <div style="display:flex; justify-content:center;">
            <div>{fig_html}</div>
        </div>

        <h2>最近12个月绩效明细</h2>

        <!-- 表格居中 -->
        <div style="display:flex; justify-content:center;">
            {table_html}
        </div>
    </body>
    </html>
    """
    return html_content


def get_last_month_folder_name():
    """获取‘当月-1’的文件夹名称，比如2025年07月运行 -> 2025年06月"""
    today = datetime.today()
    last_month_date = today.replace(day=1) - relativedelta(months=1)
    return f"{last_month_date.year}年{last_month_date.month:02d}月"


def main():
    # 1️⃣ 读取所有月份绩效数据
    df_all = load_all_monthly_data()
    if df_all.empty:
        print("❌ 没有读取到任何绩效数据，请检查路径或文件格式")
        return

    # 2️⃣ 获取所有员工姓名
    employees = df_all["员工"].unique()
    print(f"✅ 找到 {len(employees)} 位员工的绩效数据")

    # 3️⃣ 计算输出文件夹（当月-1）
    folder_name = get_last_month_folder_name()
    target_dir = os.path.join(IMAGE_RESULT_DIR, folder_name)
    os.makedirs(target_dir, exist_ok=True)
    print(f"📂 本次输出文件将保存到：{target_dir}")

    # 4️⃣ 逐员工生成图+表格HTML
    for emp in employees:
        html_content = plot_employee_bar_with_table(df_all, emp)
        save_path = os.path.join(target_dir, f"{emp}.html")
        with open(save_path, "w", encoding="utf-8") as f:
            f.write(html_content)
        print(f"✅ {emp} 的绩效图+表格已生成：{save_path}")

    print("🎉 所有员工绩效图已生成完成")


if __name__ == "__main__":
    main()
